/*
 * 版权所有 (C) 2015 知启蒙(ZHIQIM) 保留所有权利。[遇见知启蒙，邂逅框架梦]
 * 
 * https://zhiqim.org/project/zhiqim_framework/zhiqim_kernel.htm
 *
 * Zhiqim Kernel is licensed under Mulan PSL v2.
 * You can use this software according to the terms and conditions of the Mulan PSL v2.
 * You may obtain a copy of Mulan PSL v2 at:
 *          http://license.coscl.org.cn/MulanPSL2
 * THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
 * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
 * MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
 * See the Mulan PSL v2 for more details.
 */
package org.zhiqim.kernel.util;

import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.zhiqim.kernel.annotation.AnAlias;
import org.zhiqim.kernel.annotation.AnNullable;
import org.zhiqim.kernel.constants.SignConstants;
import org.zhiqim.kernel.model.maps.MapSS;

/**
 * SQL相关工具类
 *
 * @version v1.0.0 @author zouzhigang 2014-2-27 新建与整理
 */
@AnAlias("Sqls")
public class Sqls implements SignConstants
{
    /*************************************************************************************/
    //字符串格式化
    /*************************************************************************************/
    
    /** 格式化SQL语句中值的单引号为两个单引号 */
    public static String formatQuote(String sql)
    {
        return sql.replaceAll("'", "''");
    }
    
    /** 格式化SQL语句，对\r\n\t作一个空格处理，然后多个格式替换成单空格 */
    public static String formatSpace(String sql)
    {
        sql = sql.replaceAll("\n", _ONE_);
        sql = sql.replaceAll("\r", _ONE_);
        sql = sql.replaceAll("\t", _ONE_);
        
        return Strings.formatSpace(sql);
    }
    
    /**
     * 替换SQL中$$可替换的值
     * 
     * @param sql           SQL语句
     * @param replaceMap    可替换的表
     * @return              新的SQL语句
     */
    public static String formatReplaceMap(String sql, MapSS replaceMap)
    {
        if (replaceMap == null || replaceMap.isEmpty())
            return sql;
        
        return formatReplaceMap(sql, replaceMap.instance());
    }
    
    /**
     * 替换SQL中$$可替换的值
     * 
     * @param sql           SQL语句
     * @param replaceMap    可替换的表
     * @return              新的SQL语句
     */
    public static String formatReplaceMap(String sql, Map<String, String> replaceMap)
    {
        if (replaceMap == null || replaceMap.isEmpty())
            return sql;
        
        for (Entry<String, String> entry : replaceMap.entrySet())
        {
            String key = entry.getKey();
            String value = entry.getValue();
            sql = sql.replaceAll("\\$"+key+"\\$", value);
        }
        
        return sql;
    }
    
    /*************************************************************************************/
    //Timestamp & Date & Time转换为字符串
    /*************************************************************************************/
    
    @AnNullable /** 生成标准的yyyy-MM-dd HH:mm:ss */
    public static String toDateTimeString(Timestamp timestamp)
    {
        return toDateTimeString(timestamp, 0);
    }
    
    @AnNullable /** 生成UTC+8时区的yyyy-MM-dd HH:mm:ss */
    public static String toDateTimeStringUTC8(Timestamp timestamp)
    {
        return toDateTimeString(timestamp, 8);
    }
    
    /**
     * 生成标准的yyyy-MM-dd HH:mm:ss
     * 
     * @param timestamp 数据库时间类型
     * @param utc       UTC时区，如中国是UTC+8
     * @return          转化为字符串
     */@AnNullable
    public static String toDateTimeString(Timestamp timestamp, int utc)
    {
        if (timestamp == null)
            return null;
        
        return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(utc==0?timestamp:new Date(timestamp.getTime() + utc * _HOUR_));
    }
    
    @AnNullable /** 生成标准的yyyy-MM-dd */
    public static String toDateString(Timestamp timestamp)
    {
        if (timestamp == null)
            return null;
        
        return new SimpleDateFormat("yyyy-MM-dd").format(timestamp);
    }
    
    @AnNullable /** 生成标准的yyyy-MM-dd */
    public static String toDateString(Date date)
    {
        if (date == null)
            return null;
        
        return new SimpleDateFormat("yyyy-MM-dd").format(date);
    }
    
    @AnNullable /** 生成标准的HH:mm:ss */
    public static String toTimeString(Timestamp timestamp)
    {
        if (timestamp == null)
            return null;
        
        return new SimpleDateFormat("HH:mm:ss").format(timestamp);
    }
    
    @AnNullable /** 生成标准的HH:mm:ss */
    public static String toTimeString(Time time)
    {
        if (time == null)
            return null;
        
        return new SimpleDateFormat("HH:mm:ss").format(time);
    }
    
    /*************************************************************************************/
    //String转换为Date类型
    /*************************************************************************************/
    
    /** String到java.sql.Date的转换 标准格式:yyyy-MM-dd */
    public static Date toDate(String date)
    {
        return Date.valueOf(date);
    }

    /** 生成java.sql.Date,通过传入year, month, day */
    public static Date toDate(String year, String month, String day)
    {
        return Date.valueOf(year+"-"+month+"-"+day);
    }

    /** 生成java.sql.Date,通过传入year, month, day */
    public static Date toDate(int year, int month, int day)
    {
        return Date.valueOf(year+"-"+month+"-"+day);
    }
    
    /*************************************************************************************/
    //String转换为Time类型
    /*************************************************************************************/
    
    /** 转换String 到 Time,格式:"HH:mm:ss" */
    public static Time toTime(String time)
    {
        return Time.valueOf(time);
    }

    /** 生成 Time 通过输入时,分,秒 */
    public static Time toTime(String hour, String minute, String second)
    {
        return Time.valueOf(hour+":"+minute+":"+second);
    }

    /** 生成 Time 通过输入时,分,秒 */
    public static Time toTime(int hour, int minute, int second)
    {
        return Time.valueOf(hour+":"+minute+":"+second);
    }
    
    /*************************************************************************************/
    //String转换为Timestamp类型
    /*************************************************************************************/
    
    /** 返回当前时间 */
    public static Timestamp nowTimestamp()
    {
        return new Timestamp(System.currentTimeMillis());
    }
    
    /** 转换String 到 Timestamp,格式:"yyyy-MM-dd HH:mm:ss" */
    public static Timestamp toTimestamp(String datetime)
    {
        return Timestamp.valueOf(datetime);
    }
    
    /** 转换String 到 Timestamp,格式:"yyyy-MM-dd"和"HH:mm:ss" */
    public static Timestamp toTimestamp(String date, String time)
    {
        return Timestamp.valueOf(date + " " + time);
    }
    
    /** 生成 Timestamp 通过输入年,月,日,时,分,秒 */
    public static Timestamp toTimestamp(String year, String month, String day, String hour, String minute, String second)
    {
        return Timestamp.valueOf(new StringBuilder(year).append("-").append(month).append("-").append(day)
                                .append(" ").append(hour).append(":").append(minute).append(":").append(second).toString());
    }

    /** 生成 Timestamp 通过输入年,月,日,时,分,秒 */
    public static Timestamp toTimestamp(int year, int month, int day, int hour, int minute, int second)
    {
        return Timestamp.valueOf(new StringBuilder(year).append("-").append(month).append("-").append(day)
                                .append(" ").append(hour).append(":").append(minute).append(":").append(second)
                                .toString());
    }
    
    /*************************************************************************************/
    //取指定日期的00:00:00和23:59:59
    /*************************************************************************************/
    
    /** 转换日期到Timestamp，时间固定为00:00:00 */
    public static Timestamp toTimestampBegin(String date)
    {
        return toTimestamp(date, "00:00:00");
    }

    /** 转换日期到Timestamp，时间固定为23:59:59 */
    public static Timestamp toTimestampEnd(String date)
    {
        return toTimestamp(date, "23:59:59");
    }
    
    /** 返回从当日00:00:00的时间 */
    public static Timestamp toTimestampBegin(Timestamp stamp)
    {
        Calendar calendar = Calendar.getInstance();
        //指到指定的时间
        calendar.setTime(stamp);
        //设置零点零分零秒
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        
        return new Timestamp(calendar.getTimeInMillis());
    }
    
    /** 返回从当日结束的23:59:59时间 */
    public static Timestamp toTimestampEnd(Timestamp stamp)
    {
        Calendar calendar = Calendar.getInstance();
        //指到指定的时间
        calendar.setTime(stamp);
        //设置23:59:59
        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        calendar.set(Calendar.MILLISECOND, 0);
        
        return new Timestamp(calendar.getTimeInMillis());
    }
    
    /*************************************************************************************/
    //取指定时间的向前（day取值为负数）和向后的时间格式
    /*************************************************************************************/
    
    /** 返回下一天零点零分零秒的时间 */
    public static Timestamp getNextTimestampBegin(Timestamp stamp)
    {
        return getNextTimestampBegin(stamp, 1);
    }
    
    /** 返回多少天后零点零分零秒的时间 */
    public static Timestamp getNextTimestampBegin(Timestamp stamp, int day)
    {
        Calendar calendar = Calendar.getInstance();
        //指到指定的时间
        calendar.setTime(stamp);
        //设置零点零分零秒
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        //向后移天数
        calendar.add(Calendar.DAY_OF_MONTH, day);
        
        return new Timestamp(calendar.getTimeInMillis());
    }

    /** 返回从多少日后结束的23:59:59时间 */
    public static Timestamp getNextTimestampEnd(Timestamp stamp)
    {
        return getNextTimestampEnd(stamp, 1);
    }

    /** 返回从多少日后结束的23:59:59时间 */
    public static Timestamp getNextTimestampEnd(Timestamp stamp, int day)
    {
        Calendar calendar = Calendar.getInstance();
        //指到指定的时间
        calendar.setTime(stamp);
        //设置23:59:59
        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        calendar.set(Calendar.MILLISECOND, 0);
        //向后移天数
        calendar.add(Calendar.DAY_OF_MONTH, day);
        
        return new Timestamp(calendar.getTimeInMillis());
    }
    
    /*************************************************************************************/
    //初始化脚本处理
    /*************************************************************************************/
    
    /**
     * 把初始化脚本，生成一行一个SQL语句
     * 
     * @param sqls  SQL文本
     * @return      SQL列表
     */
    public static List<String> toSqlList(String sqls)
    {
        if (Validates.isEmptyBlank(sqls))
            return new ArrayList<>();
            
        List<String> sqlList = new ArrayList<>();
        
        String[] sqlArr = Arrays.toStringArray(sqls, "\n");
        for (String sql : sqlArr)
        {
            sql = Strings.trim(sql);
            if (Validates.isEmpty(sql) || Strings.startsWith(sql, "--") || Strings.startsWith(sql, "commit"))
                continue;
            
            sql = Strings.trimRight(sql, ";");
            sqlList.add(sql);
        }
        
        return sqlList;
    }
}
